package com.example.persistence.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.module.api.Card;

public class CardListHelper  extends SQLiteOpenHelper {

	private final static String TAG = "CardListHelper";

	public final static String DB_NAME = "cardlist.db";
	public final static String TABLE_NAME = "cardlist";
	public final static int DB_VERSION = 1;

	public final static String COLUMN_CARD_TOKEN = "CardToken";
	public final static String COLUMN_MASKED_CARD_NUMBER = "MaskedCardNumber";
	public final static String COLUMN_EXP_DATE = "ExpDate";
	public final static String COLUMN_POINT = "Point";
	public final static String COLUMN_BALANCE = "Balance";
	public final static String COLUMN_ACTIVATED_AMOUNT = "ActivatedAmount";
	public final static String COLUMN_CREATED_DATE = "CreatedDate";
	public final static String COLUMN_ACTIVATED_DATE = "ActivatedDate";
	public final static String COLUMN_LAST_MODIFIED_DATE = "LastModifiedDate";
	public final static String COLUMN_STATUS = "Status";
	public final static String COLUMN_NICK_NAME = "NickName";
	public final static String COLUMN_MERCHANT_ID = "MerchantId";
	public final static String COLUMN_CORPORATE_ID = "CorporateId";
	public final static String COLUMN_CORPORATE_NAME = "CorporateName";
	public final static String COLUMN_USER_KEY = "UserKey";
	public final static String COLUMN_CARD_NUMBER = "CardNumber";
	public final static String COLUMN_CORPORATE_BRAND = "CorporateBrand";
	public final static String COLUMN_CORPORATE_CARD_PICTURE_ID = "CorporateCardPictureId";
	public final static String COLUMN_CORPORATE_CARD_PICTURE_NAME = "CorporateCardPictureName";
	public final static String COLUMN_CARD_IMAGE = "CardImage";
	public final static String COLUMN_FRONT_IMAGE = "FrontImage";
	public final static String COLUMN_BACK_IMAGE = "BackImage";
	

	private final static String SQL_CREATE_TABLE = "CREATE TABLE "
			+ TABLE_NAME + " (" + COLUMN_CARD_TOKEN + " VARCHAR(10), "
			+ COLUMN_MASKED_CARD_NUMBER + " VARCHAR(20), " 
			+ COLUMN_EXP_DATE + " VARCHAR(10), " 
			+ COLUMN_POINT + " INT, " 
			+ COLUMN_BALANCE + " REAL, " 
			+ COLUMN_ACTIVATED_AMOUNT + " INT, " 
			+ COLUMN_CREATED_DATE + " VARCHAR(30), " 
			+ COLUMN_ACTIVATED_DATE + " VARCHAR(30), "
			+ COLUMN_LAST_MODIFIED_DATE + " VARCHAR(30), "
			+ COLUMN_STATUS + " VARCHAR(10), " 
			+ COLUMN_NICK_NAME + " VARCHAR(20), " 
			+ COLUMN_MERCHANT_ID + " VARCHAR(10), "
			+ COLUMN_CORPORATE_ID + " VARCHAR(10), "
			+ COLUMN_CORPORATE_NAME + " VARCHAR(20), "
			+ COLUMN_USER_KEY + " VARCHAR(10), "
			+ COLUMN_CARD_NUMBER + " VARCHAR(20), "
			+ COLUMN_CORPORATE_BRAND + " VARCHAR(20), "
			+ COLUMN_CORPORATE_CARD_PICTURE_ID + " VARCHAR(10), "
			+ COLUMN_CORPORATE_CARD_PICTURE_NAME + " VARCHAR(30), "
			+ COLUMN_CARD_IMAGE + " VARCHAR(50), "
			+ COLUMN_FRONT_IMAGE + " VARCHAR(50), "
			+ COLUMN_BACK_IMAGE + " VARCHAR(50) "
			 
			+ " )";
	
	public CardListHelper(Context _context) {
		super(_context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(TAG, "onCreate()");
		
		db.execSQL(SQL_CREATE_TABLE);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public void insert(ArrayList<Card> values) {
		Log.d(TAG, "insert()");
		
		deleteAll();
		
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues contentValues = new ContentValues();
		
		for (int i = 0; i < values.size(); i++) {
			Card value = values.get(i);
			
			contentValues.put(COLUMN_CARD_TOKEN, value.getCardToken());
			contentValues.put(COLUMN_MASKED_CARD_NUMBER, value.getMaskedCardNumber());
			contentValues.put(COLUMN_EXP_DATE, value.getExpDate());
			contentValues.put(COLUMN_POINT, value.getPoint());
			contentValues.put(COLUMN_BALANCE, value.getBalance());
			contentValues.put(COLUMN_ACTIVATED_AMOUNT, value.getActivatedAmount());
			contentValues.put(COLUMN_CREATED_DATE, value.getCreatedDate());
			contentValues.put(COLUMN_ACTIVATED_DATE, value.getActivatedDate());
			contentValues.put(COLUMN_LAST_MODIFIED_DATE, value.getLastModifiedDate());
			contentValues.put(COLUMN_STATUS, value.getStatus());
			contentValues.put(COLUMN_NICK_NAME, value.getNickName());
			contentValues.put(COLUMN_MERCHANT_ID, value.getMerchantId());
			contentValues.put(COLUMN_CORPORATE_ID, value.getCorporateId());
			contentValues.put(COLUMN_CORPORATE_NAME, value.getCorporateName());
			contentValues.put(COLUMN_USER_KEY, value.getUserKey());
			contentValues.put(COLUMN_CARD_NUMBER, value.getCardNumber());
			contentValues.put(COLUMN_CORPORATE_BRAND, value.getCorporateBrand());
			contentValues.put(COLUMN_CORPORATE_CARD_PICTURE_ID, value.getCorporateCardPictureId());
			contentValues.put(COLUMN_CORPORATE_CARD_PICTURE_NAME, value.getCorporateCardPictureName());
			contentValues.put(COLUMN_CARD_IMAGE, value.getCardImage());
			contentValues.put(COLUMN_FRONT_IMAGE, value.getFrontImage());
			contentValues.put(COLUMN_BACK_IMAGE, value.getBackImage());
			
			long newRowId = db.insert(TABLE_NAME, null, contentValues);
		}
		
		db.close();
	}

	public ArrayList<Card> getList() {
		String sql = 
				"SELECT "+
				COLUMN_CARD_TOKEN+", "+
				COLUMN_MASKED_CARD_NUMBER+", "+
				COLUMN_EXP_DATE+", "+
				COLUMN_POINT+", "+
				COLUMN_BALANCE+", "+
				COLUMN_ACTIVATED_AMOUNT+", "+
				COLUMN_CREATED_DATE+", "+
				COLUMN_ACTIVATED_DATE+", "+
				COLUMN_LAST_MODIFIED_DATE +", "+
				COLUMN_STATUS+", "+
				COLUMN_NICK_NAME+", "+
				COLUMN_MERCHANT_ID+", "+
				COLUMN_CORPORATE_ID+", "+
				COLUMN_CORPORATE_NAME+", "+
				COLUMN_USER_KEY+", "+
				COLUMN_CARD_NUMBER+", "+
				COLUMN_CORPORATE_BRAND+", "+
				COLUMN_CORPORATE_CARD_PICTURE_ID+", "+
				COLUMN_CORPORATE_CARD_PICTURE_NAME+", "+
				COLUMN_CARD_IMAGE+", "+
				COLUMN_FRONT_IMAGE+", "+
				COLUMN_BACK_IMAGE+" "+
				" FROM "+ TABLE_NAME +
				" ORDER BY " + COLUMN_CARD_TOKEN + " ASC ";
				
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		
        ArrayList<Card> cards = new ArrayList<Card>();
		while (cursor.moveToNext()) {
			Card card = new Card();
			card.setCardToken(cursor.getString(0));
			card.setMaskedCardNumber(cursor.getString(1));
			card.setExpDate(cursor.getString(2));
			card.setPoint(cursor.getInt(3));
			card.setBalance(cursor.getDouble(4));
			card.setActivatedAmount(cursor.getInt(5));
			card.setCreatedDate(cursor.getString(6));
			card.setActivatedDate(cursor.getString(7));
			card.setLastModifiedDate(cursor.getString(8));
			card.setStatus(cursor.getString(9));
			card.setNickName(cursor.getString(10));
			card.setMerchantId(cursor.getString(11));
			card.setCorporateId(cursor.getString(12));
			card.setCorporateName(cursor.getString(13));
			card.setUserKey(cursor.getString(14));
			card.setCardNumber(cursor.getString(15));
			card.setCorporateBrand(cursor.getString(16));
			card.setCorporateCardPictureId(cursor.getString(17));
			card.setCorporateCardPictureName(cursor.getString(18));
			card.setCardImage(cursor.getString(19));
			card.setFrontImage(cursor.getString(20));
			card.setBackImage(cursor.getString(21));
			
			cards.add(card);
		}
		cursor.close();
		db.close();
		
		return cards;
	}
	
	public Card getCard(String cardToken) {
		String sql = 
				"SELECT "+
				COLUMN_CARD_TOKEN+", "+
				COLUMN_MASKED_CARD_NUMBER+", "+
				COLUMN_EXP_DATE+", "+
				COLUMN_POINT+", "+
				COLUMN_BALANCE+", "+
				COLUMN_ACTIVATED_AMOUNT+", "+
				COLUMN_CREATED_DATE+", "+
				COLUMN_ACTIVATED_DATE+", "+
				COLUMN_LAST_MODIFIED_DATE +", "+
				COLUMN_STATUS+", "+
				COLUMN_NICK_NAME+", "+
				COLUMN_MERCHANT_ID+", "+
				COLUMN_CORPORATE_ID+", "+
				COLUMN_CORPORATE_NAME+", "+
				COLUMN_USER_KEY+", "+
				COLUMN_CARD_NUMBER+", "+
				COLUMN_CORPORATE_BRAND+", "+
				COLUMN_CORPORATE_CARD_PICTURE_ID+", "+
				COLUMN_CORPORATE_CARD_PICTURE_NAME+", "+
				COLUMN_CARD_IMAGE+", "+
				COLUMN_FRONT_IMAGE+", "+
				COLUMN_BACK_IMAGE+" "+
				 " FROM "+ TABLE_NAME +
				 " WHERE "+ COLUMN_CARD_TOKEN + "= '" + cardToken + "'";
				
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		
		Card card = null;
		if (cursor.moveToFirst()) {
			card = new Card();
			card.setCardToken(cursor.getString(0));
			card.setMaskedCardNumber(cursor.getString(1));
			card.setExpDate(cursor.getString(2));
			card.setPoint(cursor.getInt(3));
			card.setBalance(cursor.getDouble(4));
			card.setActivatedAmount(cursor.getInt(5));
			card.setCreatedDate(cursor.getString(6));
			card.setActivatedDate(cursor.getString(7));
			card.setLastModifiedDate(cursor.getString(8));
			card.setStatus(cursor.getString(9));
			card.setNickName(cursor.getString(10));
			card.setMerchantId(cursor.getString(11));
			card.setCorporateId(cursor.getString(12));
			card.setCorporateName(cursor.getString(13));
			card.setUserKey(cursor.getString(14));
			card.setCardNumber(cursor.getString(15));
			card.setCorporateBrand(cursor.getString(16));
			card.setCorporateCardPictureId(cursor.getString(17));
			card.setCorporateCardPictureName(cursor.getString(18));
			card.setCardImage(cursor.getString(19));
			card.setFrontImage(cursor.getString(20));
			card.setBackImage(cursor.getString(21));
			
		}
		cursor.close();
		db.close();
		
		return card;
	}

	public void deleteAll() {
		SQLiteDatabase db = getReadableDatabase();
		db.execSQL("DELETE FROM " + TABLE_NAME);
		
		db.close();
	}

	public int updateCard(Card card) {
		SQLiteDatabase db = getReadableDatabase();
		ContentValues values = new ContentValues();
		if (!"".equals(card.getCardImage()))
			values.put(COLUMN_CARD_IMAGE, card.getCardImage());
		if (!"".equals(card.getFrontImage()))
			values.put(COLUMN_FRONT_IMAGE, card.getFrontImage());
		if (!"".equals(card.getBackImage()))
			values.put(COLUMN_BACK_IMAGE, card.getBackImage());
	    
	    return db.update(TABLE_NAME, values, COLUMN_CARD_TOKEN + " = ?",
	            new String[] { String.valueOf(card.getCardToken()) });
	}

}